窗口函数的基本使用方法

一．窗口函数简介

窗口函数又名开窗函数，属于分析函数的一种。用于解决复杂报表统计需求的功能强大的函数。窗口函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。

二．窗口函数基本语句形式

Function(arg1 , ……) over(partition by query_patition_clause order by order_by_clause Window_clause )

• Window_clause : rows | range between start_expr and end_expr
• Start_expr : unbounded preceding | current row | n preceding | n following
• End_expr : unbounded following | current row | n preceding | n following

窗口函数带有一个开窗函数over()，包含三个分析子句:

• 分组(partition by)，即query_patition_clause
• 排序(order by)，即order_by_clause
• 窗口(rows)，即Window_clause

三．可使用窗口的函数

不是所有的函数都支持开窗函数。目前支持的窗口函数可结合的函数有：

• Row_number；
• 和排名函数(rank、dense_rank)连用；
• 和错行函数(lead、lag)联合使用；
• First_value和last_value；
• NTILE；
• Ratio_to_report；
• 和统计函数(max、min、avg、sum)等连用；

先构建测试表：

create table test(id int,name varchar(10),sale int);
insert into test values(1,’aaa’,100);
insert into test values(1,’bbb’,200);
insert into test values(1,’ccc’,200);
insert into test values(1,’ddd’,300);
insert into test values(2,’eee’,400);
insert into test values(2,’fff’,200);
select * from test;

Ln	ID	NAME	SALE
1	1	aaa	100
2	1	bbb	200
3	1	ccc	200
4	1	ddd	300
5	2	eee	400
6	2	fff	200

---

下面依次介绍支持的窗口函数。
3.1 ROW_NUMBER

ROW_NUMBER() OVER(partition by col1 order by col2)
表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内是连续且唯一的）。

select t.*,row_number() over(partition by id order by sale) rn from test t;

Ln	ID	NAME	SALE	RN
1	1	aaa	100	1
2	1	bbb	200	2
3	1	ccc	200	3
4	1	ddd	300	4
5	2	fff	200	1
6	2	eee	400	2

当query_patition_clause没有时，将视全部记录为一个分组。使用row_number()，order_by_clause必须有，否则报错。

---

select t.*,row_number() over(order by sale) rn from test t;

Ln	ID	NAME	SALE	RN
1	1	aaa	100	1
2	2	fff	200	2
3	1	bbb	200	3
4	1	ccc	200	4
5	1	ddd	300	5
6	2	eee	400	6

另外，如果在over中已经添加了order by…，就不建议在from后面再添加相同的order
by。二者一致的话还好(效果一样，没有差别)，但不一致时结果有时就可能令人费解了。

3.2 RANK与DENSE_RANK
rank 和 dense_rank 主要的功能是计算一组数值中的排序值。其语法格式如下:

RANK() OVER ([query_partition_clause] order_by_clause)

select t.*,rank() over(partition by id order by sale) rn from test t;

Ln	ID	NAME	SALE	RN
1	1	aaa	100	1
2	1	bbb	200	2
3	1	ccc	200	2
4	1	ddd	300	4
5	2	fff	200	1
6	2	eee	400	2

---

select t.*,dense_rank() over(partition by id order by sale) rn from test t;

Ln	ID	NAME	SALE	RN
1	1	aaa	100	1
2	1	bbb	200	2
3	1	ccc	200	2
4	1	ddd	300	3
5	2	fff	200	1
6	2	eee	400	2

rank()是跳跃排序，有两个第二名时接下来就是第四名，不会出现第三名；dense_rank()是连续排序，有两个第二名时仍然跟着第三名。

3.3 LEAD与LAG
这两个函数是偏移量函数，可以查出一个字段的上一个值或者下一个值。lead函数是向下取值，即当前行是当前分区内最后一条则显示null；lag函数是向上取值，即当前行是当前分区内第一条则显示null。其语法如下：

lead(EXPR,<OFFSET>,<DEFAULT>)
lag(EXPR,<OFFSET>,<DEFAULT>)

• EXPR通常是直接是列名，也可以是从其他行返回的表达式；
• OFFSET是默认为1，表示在当前分区内基于当前行的偏移行数；
• DEFAULT是在OFFSET指定的偏移行数超出了分组的范围时(此时会返回null)，可以通过设置这个字段来返回一个默认值来替代null。

---

 select t.*,lead(sale) over(partition by id order by sale) rn from test t;

Ln	ID	NAME	SALE	RN
1	1	aaa	100	200
2	1	bbb	200	200
3	1	ccc	200	300
4	1	ddd	300	NULL
5	2	fff	200	400
6	2	eee	400	NULL

---

select t.*,lag(sale) over(partition by id order by sale) rn from test t;

Ln	ID	NAME	SALE	RN
1	1	aaa	100	NULL
2	1	bbb	200	100
3	1	ccc	200	200
4	1	ddd	300	200
5	2	fff	200	NULL
6	2	eee	400	200

---

select t.*,lead(sale,2) over(partition by id order by sale) rn from test t;

Ln	ID	NAME	SALE	RN
1	1	aaa	100	200
2	1	bbb	200	300
3	1	ccc	200	NULL
4	1	ddd	300	NULL
5	2	fff	200	NULL
6	2	eee	400	NULL

---

select t.*,lead(sale,2,500) over(partition by id order by sale) rn from test t;

Ln	ID	NAME	SALE	RN
1	1	aaa	100	200
2	1	bbb	200	300
3	1	ccc	200	500
4	1	ddd	300	500
5	2	fff	200	500
6	2	eee	400	500

---

select t.*,lead(2) over(partition by id order by sale) rn from test t;

Ln	ID	NAME	SALE	RN
1	1	aaa	100	2
2	1	bbb	200	2
3	1	ccc	200	2
4	1	ddd	300	NULL
5	2	fff	200	2
6	2	eee	400	NULL

如果只指定一个常量，则将该值直接作为结果返回(偏移行数默认为1)，但分区内最后一行仍然为null(同理，lag时分区内第一行也是null)。

---

select t.*,lead(2,2) over(partition by id order by sale) rn from test t;

Ln	ID	NAME	SALE	RN
1	1	aaa	100	2
2	1	bbb	200	2
3	1	ccc	200	NULL
4	1	ddd	300	NULL
5	2	fff	200	NULL
6	2	eee	400	NULL

如果指定2个常量，也是将该值直接作为结果返回(第一个参数为偏移行数)，这样分区内最后两行仍然为null(同理，lag时分区内前两行也是null)。

---

select t.*,lead(2,2,2) over(partition by id order by sale) rn from test t;

Ln	ID	NAME	SALE	RN
1	1	aaa	100	2
2	1	bbb	200	2
3	1	ccc	200	2
4	1	ddd	300	2
5	2	fff	200	2
6	2	eee	400	2

如果是3个参数都是常量，则会把最后一个常量当作默认值替代null。(这两个函数的确挺有趣的！)

3.4 FIRST_VALUE与LAST_VALUE
FIRST_VALUE返回一组排序值后的第一个值，LAST_VALUE返回一组排序值后的最后一个值。其语法如下：

FIRST_VALUE( expr ) OVER ( analytic_clause )
LAST_VALUE( expr ) OVER ( analytic_clause )

---

select t.*,first_value(sale) over(partition by id order by sale) rn from test t;

Ln	ID	NAME	SALE	RN
1	1	aaa	100	100
2	1	bbb	200	100
3	1	ccc	200	100
4	1	ddd	300	100
5	2	fff	200	200
6	2	eee	400	200

---

select t.*,first_value(2) over(partition by id order by sale) rn from test t;

Ln	ID	NAME	SALE	RN
1	1	aaa	100	2
2	1	bbb	200	2
3	1	ccc	200	2
4	1	ddd	300	2
5	2	fff	200	2
6	2	eee	400	2

可以接收一个常量参数，效果等同于直接将该值作为结果返回。

---

select t.*,last_value(sale) over(partition by id order by sale) rn from test t;

Ln	ID	NAME	SALE	RN
1	1	aaa	100	100
2	1	bbb	200	200
3	1	ccc	200	200
4	1	ddd	300	300
5	2	fff	200	200
6	2	eee	400	400

这两个函数可以不跟order by子句，也可以指定窗口上下界。

3.5 NTILE
NTILE函数对一个数据分区中的有序结果集进行划分，将其分组到各个桶，并为每个小组分配一个唯一的组编号。这个函数在统计分析中是很有用的。例如，如果想移除异常值,我们可以将它们分组到顶部或底部的桶中，然后在统计分析的时候将这些值排除。在统计信息收集可以使用NTILE函数来计算直方图信息边界。在统计学术语中，NTILE函数创建等宽直方图信息。其语法如下：

NTILE(ntile_num) OVER ( analytic_clause )

其中ntile_num不能小于等于0。analytic_clause中必须有order by子句。

select t.*,ntile(3) over(order by sale) rn from test t;

Ln	ID	NAME	SALE	RN
1	1	aaa	100	1
2	2	fff	200	1
3	1	bbb	200	2
4	1	ccc	200	2
5	1	ddd	300	3
6	2	eee	400	3

3.6 RATIO_TO_REPORT
ratio_to_report函数用来计算总数百分比。通常计算百分比的方法是在总计报告的子查询中使用SUM函数总计报告，然后把那个结果放到细节表中相除来计算百分比。还可以用一个子查询作为SELECT语句表达式。RATIO_TO_REPORT函数使得这种类型的查询更容易编写。其语法如下：

RATIO_TO_REPORT (expr) OVER (query_partition_clause)

select t.*,ratio_to_report(sale) over(partition by id) rn from tes t;

Ln	ID	NAME	SALE	RN
1	1	aaa	100	0.125
2	1	bbb	200	0.25
3	1	ccc	200	0.25
4	1	ddd	300	0.375
5	2	fff	200	0.333
6	2	eee	400	0.667

此函数不能跟order by子句。

3.7统计函数(MAX、MIN、AVG、SUM、COUNT)
统计函数最为常见，这里只列举例子，语法形式如下：
SUM[MAX][MIN][AVG] OVER (query_partition_clause)

select t.*,sum(sale) over(partition by id order by sale) rn from test t;
select t.*,sum(sale) over(partition by id order by sale range between unbounded preceding and current row) rn from test t;

（注：以上二者是等效的）

Ln	ID	NAME	SALE	RN
1	1	aaa	100	100
2	1	bbb	200	500
3	1	ccc	200	500
4	1	ddd	300	800
5	2	fff	200	200
6	2	eee	400	600

---

select t.*,sum(sale) over(partition by id order by sale rows between unbounded preceding and current row) rn from test t;

Ln	ID	NAME	SALE	RN
1	1	aaa	100	100
2	1	bbb	200	300
3	1	ccc	200	500
4	1	ddd	300	800
5	2	fff	200	200
6	2	eee	400	600

---

select t.*,max(sale) over(partition by id order by sale) rn from test t;

Ln	ID	NAME	SALE	RN
1	1	aaa	100	100
2	1	bbb	200	200
3	1	ccc	200	200
4	1	ddd	300	300
5	2	fff	200	200
6	2	eee	400	400

---

select t.*,min(sale) over(partition by id order by sale) rn from test t;

Ln	ID	NAME	SALE	RN
1	1	aaa	100	100
2	1	bbb	200	100
3	1	ccc	200	100
4	1	ddd	300	100
5	2	fff	200	200
6	2	eee	400	200

四．Window_clause窗口子句

本章详细介绍下Window_clause。即窗口子句，它标识了在分组上的一个范围。其中，

• between…and用来指定窗口的起始点和终结点；
• Unbounded preceding指明窗口开始于分组的第一行；
• Current row作为起始点，指明窗口开始于当前行或当前值；作为终结点，指明窗口结束于当前行或当前值；
• Unbounded following指明窗口结束于分组的最后一行；

range是逻辑窗口，是指定当前行对应值的范围取值，行数不固定，只要行值在范围内，对应行都包含在内。rows是物理窗口，即根据order by 子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关）。

Oracle对窗口子句的逻辑限定：
*Cause: If the window specification is specified
using RANGE option and there are multiple ORDER BY expressions, then
the aggregation group cannot contain any expression (It can only
have CURRENT ROW, UNBOUNDED PRECEDING, or UNBOUNDED FOLLOWING).
First end point (bound) cannot be UNBOUNDED FOLLOWING and second end
point cannot be UNBOUNDED PRECEDING. If the first end point is
CURRENT ROW, then second end point can only be CURRENT ROW or
/UNBOUNDED FOLLOWING. If the first end point is FOLLOWING,
then second end point can only be /UNBOUNDED FOLLOWING.

即，如果使用窗口说明中使用了range/rows以及order by子句时，聚合组里只能含有CURRENT ROW, UNBOUNDED PRECEDING或UNBOUNDED FOLLOWING。
下界点(the first end point)不能是UNBOUNDED FOLLOWING；上界点(second end point)不能是UNBOUNDED PRECEDING。
如果下界点是CURRENT ROW，那么上界点只能是CURRENT ROW或者UNBOUNDED FOLLOWING或者 FOLLOWING；如果下界点是 FOLLOWING，那么上界点只能是 / UNBOUNDED FOLLOWING。

编者按：窗口说明中起始点与终结点之间排列组合顺序(由下限到上限)依次为：
UNBOUNDED PRECEDING， PRECEDING，CURRENT ROW， FOLLOWING，UNBOUNDED FOLLOWING。
因此只允许BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，而不允许BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING。其他情况相同。
目前除了聚合函数可以指定窗口上下界之外，非聚合函数只有first_value和last_value函数可以指定。其他的函数都不能指定。

五．补充说明

• 当sql语句中的order by子句里的内容和开窗函数over()中的order by子句里的内容一样，那么sql语句中的排序将先执行,分析函数在分析时就不必再排序；order by 字段两者不一致时，那么sql语句中的排序将最后在分析函数分析结束后执行排序。所以如果在over中已经添加了order by…，就不建议在from后面再添加相同的order by。二者一致的话还好(效果一样，没有差别)，但不一致时结果有时就可能令人费解了。
• 窗口子句必须和order by 子句同时使用，且如果指定了order by 子句未指定窗口子句，则默认为RANGE BETWEEN unbounded preceding AND CURRENT ROW。
• 如果分析函数没有指定ORDER BY子句，也就不存在ROWS/RANGE窗口的计算。